package net.cl.db.core;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.cl.db.CachedResultSet;
import net.cl.db.DBException;
import net.cl.db.DBExecutor;
import net.cl.db.TableProperties;
import net.cl.db.Constants.DBTYPE;
import net.cl.db.ds.BasicDataSource;
import net.cl.db.handler.RowHandler;
import net.cl.db.sql.SqlBuilder;
import net.cl.db.sql.dialect.Dialect;

public class DBExecutorImpl implements DBExecutor {

	private static final Log log = LogFactory.getLog(DBExecutorImpl.class);
	private Connection conn;
	private Dialect dialect;

	public DBExecutorImpl(Connection conn) {
		this.conn = conn;
	}

	public DBExecutorImpl(DataSource ds) throws DBException {
		if (ds == null)
			throw new NullPointerException();
		try {
			this.conn = ds.getConnection();
		} catch (SQLException e) {
			throw new DBException(e);
		}
		this.dialect = DBTools.guessDialect(ds);
	}

	@Override
	public Connection getConnection() {
		return conn;
	}

	@Override
	public CachedResultSet query(SqlBuilder sql) throws DBException {
		return query(sql.toString());
	}

	@Override
	public CachedResultSet query(String sql) throws DBException {
		try {
			Statement st = createStatement();
			ResultSet rs = st.executeQuery(sql);
			CachedResultSet crs = new CachedResultSetImpl(rs);
			closeQuietly(rs);
			closeQuietly(st);
			return crs;
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	@Override
	public int execute(String sql) throws DBException {
		try {
			Statement st = createStatement();
			int result = st.executeUpdate(sql);
			closeQuietly(st);
			return result;
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	@Override
	public int execute(SqlBuilder sql) throws DBException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void beginTransaction() throws DBException {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	@Override
	public void commit() throws DBException {
		try {
			conn.commit();
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				log.error(e);
			}
		}
	}

	@Override
	public void rollback() throws DBException {
		try {
			conn.rollback();
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				log.error(e);
			}
		}
	}

	@Override
	public void close() {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
		}
	}

	@Override
	public Dialect getDialect() {
		return dialect;
	}

	private Statement createStatement() throws SQLException {
		return conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	}

	private PreparedStatement createPStatement(String sql) throws SQLException {
		return conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	}

	private void setPStatementParas(PreparedStatement ps, Object[] paras) throws SQLException {
		if (ps == null)
			throw new NullPointerException();
		if (paras == null)
			return;
		for (int i = 0; i < paras.length; i++) {
			Object v = paras[i];
			ps.setObject(i + 1, v);
		}
	}

	private void closeQuietly(Statement st) {
		try {
			if (st != null) {
				st.clearWarnings();
				st.close();
			}
		} catch (SQLException e) {
		}
	}

	private void closeQuietly(ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
		}
	}

	public DatabaseMetaData getDBMetaData() {
		try {
			return conn.getMetaData();
		} catch (SQLException e) {
			log.error(e);
			return null;
		}
	}

	// public Object[][] queryForArray(String sql) {
	// return queryForArray(sql, 0, 0);
	// }
	//
	// public Object[][] queryForArray(String sql, int start, int rows) {
	// Object[][] array = ResultSetHandler.toObjectArray(getResultSet(sql,
	// start, rows));
	// return array;
	// }
	//
	// public List<Map<String, Object>> queryForList(String sql) {
	// return queryForList(sql, 0, 0);
	// }
	//
	// public List<Map<String, Object>> queryForList(String sql, int start, int
	// rows) {
	// List<Map<String, Object>> list =
	// ResultSetHandler.toList(getResultSet(sql, start, rows));
	// return list;
	// }
	//
	// public Object queryForObject(String sql) {
	// Object obj = ResultSetHandler.toMap(getResultSet(sql, 0, 0));
	// return obj;
	// }

	public int update(String sql, Object[] paras) {
		int rows = -1;
		try {
			if (dialect.getDbType() == DBTYPE.SQLITE) {
				if (paras != null)
					throw new IllegalArgumentException("PreparedStatement is not support sqlite.");
				Statement st = createStatement();
				rows = st.executeUpdate(sql);
				closeQuietly(st);
			} else {
				PreparedStatement ps = createPStatement(sql);
				setPStatementParas(ps, paras);
				rows = ps.executeUpdate(sql);
				closeQuietly(ps);
			}
		} catch (SQLException e) {
			log.error(e);
		}
		return rows;
	}

	public int delete(String sql, Object[] paras) {
		return update(sql, paras);
	}

	// public Object insert(String sql, Object[] paras) {
	// update(sql, paras);
	// Object obj = ResultSetHandler.toMap(getResultSet(sql, 0, 0));
	// return obj;
	// }

	// public int[] batchInsert(String[] sqls) {
	// int[] rows = { -1 };
	// try {
	// Statement st = createStatement();
	// if (sqls == null)
	// sqls = new String[0];
	// for (String sql : sqls) {
	// if (StringUtils.isEmpty(sql) || sql.startsWith("--"))
	// continue;
	// st.addBatch(sql);
	// }
	// beginTransaction();
	// rows = st.executeBatch();
	// commit();
	// closeStatement(st);
	// } catch (SQLException e) {
	// e.printStackTrace();
	// rollback();
	// }
	// return rows;
	// }

	// public int[] batchInsert(String sql, List<Object[]> paras) {
	// if (DBFactory.getDbType() == DBTYPE.SQLITE)
	// throw new IllegalArgumentException("Not support sqlite.");
	// int[] rows = { -1 };
	// try {
	// PreparedStatement ps = createPStatement(sql);
	// if (paras != null) {
	// for (int i = 0; i < paras.size(); i++) {
	// Object[] para = (Object[]) paras.get(i);
	// setPStatementParas(ps, para);
	// ps.addBatch();
	// }
	// }
	// beginTransaction();
	// rows = ps.executeBatch();
	// commit();
	// closeStatement(ps);
	// } catch (SQLException e) {
	// e.printStackTrace();
	// rollback();
	// }
	// return rows;
	// }

//	public TableProperties getTableProperties(String tableName) throws DBException {
//		String sql = "select * from " + tableName + " where 1 = 0";
//		ResultSet rs = query(sql);
//		try {
//			TableProperties table = RowHandler.getTableProperties(rs, tableName);
//			return table;
//		} catch (SQLException e) {
//			throw new DBException(e);
//		}
//	}

}
